/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.entities.Nanny;
import com.aptech.childcare.gui.ViewEditClassChildDlg;
import com.aptech.childcare.until.ConfigDatabase;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Xuan Hieu
 */
public class NannyDAO {

    ViewEditClassChildDlg view;
    private String lastError;
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ConfigDatabase db = null;
    final String SQL_CREATE = "INSERT INTO tbNanny VALUES(?,?,?,?,?,?,?,?,?,?,?)";
    final String SQL_READ = "SELECT * FROM tbNanny";
    final String SQL_DELETE = "DELETE FROM tbNanny WHERE NannyID = ?";
    final String sqlUPDATE = "SELECT * FROM tbNanny WHERE NannyID = ?";

    public NannyDAO() {
        db = new ConfigDatabase();
    }

    /**
     *
     * @param na the Nanny to add
     * @return true if add successful, false if add fail
     */
    public boolean create(Nanny na) throws IOException{
        if (!check(na)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareCall(SQL_CREATE);
            pst.setInt(1, na.getClassID());
            pst.setString(2, na.getFullName());
            pst.setString(3, na.getSex());
            pst.setString(4, na.getPathImage());
            pst.setDate(5, na.getBirthday());
            pst.setString(6, na.getAddress());
            pst.setString(7, na.getPhone());
            pst.setString(8, na.getEmail());
            pst.setFloat(9, na.getCharge());
            pst.setInt(10, na.getWorkinghours());
            pst.setDate(11, na.getStartDay());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add fail!");
        return false;
    }

    public boolean check(Nanny na)  throws IOException{
        ArrayList<Nanny> list = readByAll();
        for (Nanny ny : list) {
            if (na.getFullName().equals(ny.getFullName())) {
                setLastError("Nanny existed !");
                return false;
            }
        }
        return true;
    }

    /**
     * 
     * @param na the Nanny to edit
     * @return true if edit successful, false if edit fail
     */
    public boolean update(Nanny na) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, na.getNannyID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(2, na.getClassID());
            rs.updateString(3, na.getFullName());
            rs.updateString(4, na.getSex());
            rs.updateDate(6, na.getBirthday());
            rs.updateString(7, na.getAddress());
            rs.updateString(8, na.getPhone());
            rs.updateString(9, na.getEmail());
            rs.updateFloat(10, na.getCharge());
            rs.updateInt(11, na.getWorkinghours());
            rs.updateDate(12, na.getStartDay());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Update Fail!");
        return false;
    }
    /**
     *
     * @return list of Nanny in database
     */
    public ArrayList<Nanny> readByAll()  throws IOException{
        ArrayList<Nanny> list = new ArrayList<Nanny>();
        try {
            con = db.getConnection();
            pst = con.prepareStatement(SQL_READ);
            rs = pst.executeQuery();
            while (rs.next()) {
                Nanny na = new Nanny();
                na.setNannyID(rs.getInt(1));
                na.setClassID(rs.getInt(2));
                na.setFullName(rs.getString(3));
                na.setSex(rs.getString(4));
                na.setPathImage(rs.getString(5));
                na.setBirthday(rs.getDate(6));
                na.setAddress(rs.getString(7));
                na.setPhone(rs.getString(8));
                na.setEmail(rs.getString(9));
                na.setCharge(rs.getFloat(10));
                na.setWorkinghours(rs.getInt(11));
                na.setStartDay(rs.getDate(12));
                list.add(na);
            }

        } catch (SQLException ex) {
            setLastError("SQL error!");

        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    /**
     *
     * @param nannyID the Nanny to delete
     * @return true if delete successful, false if delete fail.
     */
    public boolean delete(int nannyID)  throws IOException{
        try {
            con = db.getConnection();
            pst = con.prepareStatement(SQL_DELETE);
            pst.setInt(1, nannyID);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Fail!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Delete fail!");
        return false;
    }

    /**
     *
     * @param naName the Nanny needed search
     * @return list of nanny
     */
    public ArrayList<Nanny> readByName(String Name)  throws IOException{
        ArrayList<Nanny> list = readByAll();
        ArrayList<Nanny> naList = new ArrayList<Nanny>();
        for (Nanny na : list) {
            if (na.getFullName().toLowerCase().matches(".*" + Name.trim().toLowerCase() + ".*")) {
                naList.add(na);
            }
        }
        return naList;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
